package com.siqi.hr.system.common.chan;

import com.siqi.hr.common.utils.Const;
import com.siqi.hr.common.utils.StringUtil;
import com.siqi.hr.system.common.authority.DataScope;
import com.siqi.hr.system.common.sql.SField;
import com.siqi.hr.system.common.sql.STable;
import com.siqi.hr.system.common.utils.CollectionUtils;
import com.siqi.hr.system.organization.entity.Dept;
import com.siqi.hr.system.organization.entity.Employee;
import com.siqi.hr.system.organization.entity.RoleEntityScope;
import org.apache.commons.lang.StringUtils;

import java.util.LinkedHashSet;
import java.util.Set;

/**
 * 拼接客户化实体列表数据sql帮助类(xiaogq)
 * @author     xiaogq
 * @date       2016-08-23
 */
public class TSQLSelectStat implements BeSql {
	/**
	 * 存放表字段部分sql
	 */
	protected FieldChan fieldChan;
	/**
	 * 存放连表部分sql
	 */
	protected TableChan tableChan;
	/**
	 * 存放过滤条件部分sql
	 */
	protected ConditionChan conditionChan = new ConditionChan();
	/**
	 * 排序部分sql
	 */
	protected String orderby;
	/**
	 * 分页部分sql
	 */
	protected String limit;
	/**
	 * 存放数据权限标识
	 */
	protected RoleEntityScope scope;
	/**
	 * 实体id
	 */
	protected Integer tableCode;
	/**
	 * 当前登录系统用户对象信息
	 */
	protected Employee employee;
	/**
	 * 当前登录系统用户所在的部门
	 */
	protected Dept dept;
	/**
	 * 存放数据权限部分sql
	 */
	protected String authorityCond = "";
	/**
	 * 存放左，右连接表集合
	 */
	public Set<String> joinTableSet = new LinkedHashSet<String>();
	/**
	 * 是否使用bootstrap模糊查询(true:是，false:否)
	 */
	protected boolean isBootstrapQuery;
	/**
	 * bootstrap模糊查询值
	 */
	protected String SearchVal;

	public StringBuilder toSqlString() {
		if (this.fieldChan == null || this.fieldChan.size() == 0){
			throw new IllegalArgumentException("字段为空");
		}
		StringBuilder sb = new StringBuilder();
		sb.append(" select distinct ").append(this.fieldChan.toSqlString()).append(" from ").append(this.tableChan.toSqlString());

		getJoinTableByName(this.tableChan);

		authCondAppend(sb);

		sb.append(" where 1=1 ");

		if (this.conditionChan.size() > 0) {
			sb.append(" and ").append(this.conditionChan.toSqlString());
		}

		if (!this.authorityCond.equals("")) {
			sb.append(" and ").append(this.authorityCond);
		}
        //如果是使用bootstrap自带模糊查询功能
		if(this.isBootstrapQuery){
			appendBootstrapFilterSql(sb);
		}

		if ((this.orderby != null) && (!this.orderby.trim().equals(""))) {
			sb.append(" order by ").append(this.orderby);
		}

		if ((this.limit != null) && (!this.limit.trim().equals(""))) {
			sb.append(" limit " + this.limit);
		}
		return sb;
	}

	public void init() {
		if (this.tableChan == null) {
			this.tableChan = new TableChan(((SField) CollectionUtils.getIndexElement(this.fieldChan, 0)).getTable());
		}

		STable main = this.tableChan.getMianTable();
		for (SField field : this.fieldChan) {
			if (field.getTable() != main) {
				STable stable = field.getTable();
				stable.setOfByName(main.getByName());
				this.tableChan.add(stable);
			}
		}
	}

	protected void authCondAppend(StringBuilder sb) {
		if ((this.scope != null) && (StringUtils.isNotEmpty(this.scope.getViewLevel()))) {
			String scopeLevel = this.scope.getViewLevel();
			String mainTable = "t_" + this.tableCode;
			String relateTable = "z_" + this.tableCode + "_" + 3;
			String empTable = "t_3";
			String deptTable = "t_2";

			if (this.tableCode.intValue() == 3 || this.tableCode.intValue() == 4) {
				if (scopeLevel.equals(DataScope.NONE.toString())) {
					this.authorityCond = " 1=0 ";
				} else if ((scopeLevel.equals(DataScope.RELATED_DEPT.toString())) || (scopeLevel.equals(DataScope.CHARGE_EMPLOYEE.toString()))) {
					this.authorityCond = (mainTable + ".pk_t_3_3 = '" + this.employee.getId() + "' ");
				} else if (scopeLevel.equals(DataScope.SIBLING.toString())) {
					this.authorityCond = (" ( " + empTable + ".pk_t_2_1 = '" + this.employee.getDept().getId() + "' " + " or "
						+ mainTable + ".pk_t_3_3 = '" + this.employee.getId() + "' ) ");
				} else if (scopeLevel.equals(DataScope.SIBLING_AND_SUBORDINATE.toString())) {//查询本级及下级数据
					sb.append(" left join " + deptTable + " as " + deptTable + "_a" + " on " + deptTable + "_a" + ".id = " + mainTable + ".pk_t_2_1 ");
					this.authorityCond = (deptTable + "_a" +".dept_rank_varchar like '" + this.dept.getDeptRank() + "%'");
				}
			} else {
				String authorityTable = "";
				if (scopeLevel.equals(DataScope.NONE.toString())) {
					this.authorityCond = " 1=0 ";
				} else if (scopeLevel.equals(DataScope.CHARGE_EMPLOYEE.toString())) {
					authorityTable = " left join " + relateTable + " as " + relateTable + "_a" + " on " + mainTable
						+ ".id = " + relateTable + "_a" + ".host_record_id ";
					sb.append(authorityTable);

					this.authorityCond = (relateTable + "_a" + ".related_type_id = " + Const.CHARGE_EMPLOYEE_ID + " and " + relateTable + "_a"
						+ ".related_record_id = '" + this.employee.getId() + "' ");
				} else if (scopeLevel.equals(DataScope.RELATED_DEPT .toString())) {
					authorityTable = " left join " + relateTable + " as " + relateTable + "_a" + " on " + mainTable
						+ ".id = " + relateTable + "_a" + ".host_record_id ";
					sb.append(authorityTable);

					this.authorityCond = (relateTable + "_a" + ".related_record_id = '" + this.employee.getId() + "' ");
				} else if (scopeLevel.equals(DataScope.SIBLING.toString())) {
					this.authorityCond = (" exists ( select 0 from " + relateTable + " as zr " + " INNER JOIN " + empTable
						+ " as soe on soe.id = zr.related_record_id" + " where " + mainTable + ".id = zr.host_record_id and soe.pk_t_2_1 = '"
						+ this.employee.getDept().getId() + "' ) ");
				} else if (scopeLevel.equals(DataScope.SIBLING_AND_SUBORDINATE.toString())) {
					this.authorityCond = (" exists ( select 0 from " + relateTable + " as zr " + " INNER JOIN " + empTable
						+ " as soe on soe.id = zr.related_record_id" + " INNER JOIN " + deptTable + " as sod on sod.id = soe.pk_t_2_1 "
						+ " where " + mainTable + ".id = zr.host_record_id ) ");
				}
			}
		}
	}

	//拼接bootstrap自带模糊查询sql条件方法xiaogq
	protected void appendBootstrapFilterSql(StringBuilder sb) {
		if(StringUtils.isNotBlank(this.getSearchVal())){
			StringBuffer filterSql = new StringBuffer();
			if(this.fieldChan.size() > 0){
				filterSql.append(" and (1 = 0 ");
				for (SField field : this.fieldChan) {
					if(StringUtils.isEmpty(field.getByName())){
						if(field.getName().startsWith("pk_t_")){
							filterSql.append(" or "+field.getName()+".name_varchar like '%"+this.getSearchVal()+"%' ");
						}else if(field.getName().endsWith("_singlebox")){
							filterSql.append(" or "+field.getName()+".name like '%"+this.getSearchVal()+"%'");
						}else if(field.getName().endsWith("_decimal")){
							filterSql.append(" or "+field.getTable().getName()+"."+field.getName()+"='"+this.getSearchVal()+"' ");
						}else if(field.getName().endsWith("_bit") || field.getName().equals("id")){
							continue;
						}else if(field.getName().endsWith("_datetime") || field.getName().equals("_date")){
							boolean bool = StringUtil.hasChinese(this.getSearchVal());
							if(!bool){
								filterSql.append(" or "+field.getTable().getName()+"."+field.getName()+" like '%"+this.getSearchVal()+"%' ");
							}
						}else{
							filterSql.append(" or "+field.getTable().getName()+"."+field.getName()+" like '%"+this.getSearchVal()+"%' ");
						}
					}
				}
				filterSql.append(") ");
			}
			sb.append(filterSql);
		}
	}

	public FieldChan getFieldChan() {
		return this.fieldChan;
	}

	public void setFieldChan(FieldChan fieldChan) {
		this.fieldChan = fieldChan;
	}

	public TableChan getTableChan() {
		return this.tableChan;
	}

	public void setTableChan(TableChan tableChan) {
		this.tableChan = tableChan;
	}

	public RoleEntityScope getScope() {
		return this.scope;
	}

	public void setScope(RoleEntityScope scope) {
		this.scope = scope;
	}

	public ConditionChan getConditionChan() {
		return this.conditionChan;
	}

	public void setConditionChan(ConditionChan conditionChan) {
		this.conditionChan = conditionChan;
	}

	public String getOrderby() {
		return this.orderby;
	}

	public void setOrderby(String orderby) {
		this.orderby = orderby;
	}

	public String getLimit() {
		return this.limit;
	}

	public void setLimit(String limit) {
		this.limit = limit;
	}

	public Integer getTablecode() {
		return this.tableCode;
	}

	public void setTablecode(Integer tablecode) {
		this.tableCode = tablecode;
	}

	public Employee getEmployee() {
		return this.employee;
	}

	public void setEmployee(Employee employee) {
		this.employee = employee;
	}

	public Dept getDept() {
		return this.dept;
	}

	public void setDept(Dept dept) {
		this.dept = dept;
	}

	public Set<String> getJoinTableSet() {
		return this.joinTableSet;
	}

	public void setJoinTableSet(Set<String> joinTableSet) {
		this.joinTableSet = joinTableSet;
	}

	protected void getJoinTableByName(TableChan tableChan) {
		for (STable stable : tableChan) {
			this.joinTableSet.add(stable.getByName());
		}
	}

	public boolean isBootstrapQuery() {
		return isBootstrapQuery;
	}

	public void setBootstrapQuery(boolean bootstrapQuery) {
		isBootstrapQuery = bootstrapQuery;
	}

	public String getSearchVal() {
		return SearchVal;
	}

	public void setSearchVal(String searchVal) {
		SearchVal = searchVal;
	}
}
